package org.unisinos.gc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.unisinos.gc.vo.Autor;

public abstract class AutorDAO {
	
	public static List<Autor> getAutorList() {
		List<Autor> autores = new ArrayList<Autor>();
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			con = ConnectionManager.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select * from Autor");
			while(rs.next()) {
				Autor c = new Autor();
				c.setCodigo(rs.getInt("codigo"));
				c.setNome(rs.getString("nome"));
				c.setEmail(rs.getString("email"));
				c.setTelefone(rs.getString("telefone"));
				c.setCpf(rs.getString("cpf"));
				autores.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (con!=null)con.close();
				if (stmt!=null)stmt.close();
				if (rs!=null)rs.close();
			}catch (Exception e) {}
		}
		return autores;
	}
	
	public static List<Autor> getAutorListIgnoring(int codAutorToIgnore) {
		List<Autor> autores = new ArrayList<Autor>();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = ConnectionManager.getConnection();
			ps = con.prepareStatement("select * from Autor where codigo != ?");
			ps.setInt(1, codAutorToIgnore);
			rs = ps.executeQuery();
			while(rs.next()) {
				Autor c = new Autor();
				c.setCodigo(rs.getInt("codigo"));
				c.setNome(rs.getString("nome"));
				c.setEmail(rs.getString("email"));
				c.setTelefone(rs.getString("telefone"));
				c.setCpf(rs.getString("cpf"));
				autores.add(c);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (con!=null)con.close();
				if (ps!=null)ps.close();
				if (rs!=null)rs.close();
			}catch (Exception e) {}
		}
		return autores;
	}
	
	public static Autor getAutor(int codigo) {
		Autor autor = null;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = ConnectionManager.getConnection();
			ps = con.prepareStatement("select * from autor where codigo = ?");
			ps.setInt(1, codigo);
			rs = ps.executeQuery();
			while(rs.next()) {
				autor = new Autor();
				autor.setCodigo(rs.getInt("codigo"));
				autor.setNome(rs.getString("nome"));
				autor.setEmail(rs.getString("email"));
				autor.setTelefone(rs.getString("telefone"));
				autor.setCpf(rs.getString("cpf"));
				break;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (con!=null)con.close();
				if (ps!=null)ps.close();
				if (rs!=null)rs.close();
			}catch (Exception e) {}
		}
		return autor;
	}
	
	public static void updateAutor(Autor autor) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionManager.getConnection();
			ps = con.prepareStatement("UPDATE autor " +
										" SET nome=?," +
										"	  email=?," +
										"	  telefone=?," +
										"	  cpf=?" +
									"	WHERE codigo=?");
			ps.setString(1, autor.getNome());
			ps.setString(2, autor.getEmail());
			ps.setString(3, autor.getTelefone());
			ps.setString(4, autor.getCpf());
			ps.setInt(5, autor.getCodigo());
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (con!=null)con.close();
				if (ps!=null)ps.close();
			}catch (Exception e) {}
		}
	}
	
	public static void deleteAutor(int codigo) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionManager.getConnection();
			ps = con.prepareStatement("delete from autor where codigo = ?");
			ps.setInt(1, codigo);
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (con!=null)con.close();
				if (ps!=null)ps.close();
			}catch (Exception e) {}
		}
	}
	
	public static void saveNewAutor(Autor autor) {
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = ConnectionManager.getConnection();
			ps = con.prepareStatement("INSERT INTO autor(codigo, nome, email, telefone, cpf) " +
									"  VALUES (?, ?, ?, ?, ?)");
			ps.setInt(1, autor.getCodigo());
			ps.setString(2, autor.getNome());
			ps.setString(3, autor.getEmail());
			ps.setString(4, autor.getTelefone());
			ps.setString(5, autor.getCpf());
			ps.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (con!=null)con.close();
				if (ps!=null)ps.close();
			}catch (Exception e) {}
		}
	}
	
	public static Autor getAutorByCpf(String cpf) {
		Autor autor = null;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = ConnectionManager.getConnection();
			ps = con.prepareStatement("select * from autor where cpf = ?");
			ps.setString(1, cpf);
			rs = ps.executeQuery();
			while(rs.next()) {
				autor = new Autor();
				autor.setCodigo(rs.getInt("codigo"));
				autor.setNome(rs.getString("nome"));
				autor.setEmail(rs.getString("email"));
				autor.setTelefone(rs.getString("telefone"));
				autor.setCpf(rs.getString("cpf"));
				break;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				if (con!=null)con.close();
				if (ps!=null)ps.close();
				if (rs!=null)rs.close();
			}catch (Exception e) {}
		}
		return autor;
	}
}
	